using System;
using System.Data;
using System.Data.SqlClient;
using DTN.Core.Common;
using DTN.Core.Utilities;
namespace com.dtn.deals
{
    public class NewFullTextDB
    {
        public static DataTable GetAll()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("NewFullText_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static void Delete(int id)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("NewFullText_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@Id", id);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static int Insert(NewFullTextInfo NewFullTextInfo)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("NewFullText_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@PartnerId", NewFullTextInfo.PartnerId);
            dbCmd.Parameters.Add("@DealId", NewFullTextInfo.DealId);
            dbCmd.Parameters.Add("@Content", NewFullTextInfo.Content);
            dbCmd.Parameters.Add("@SDateDeal", NewFullTextInfo.SDateDeal);
            dbCmd.Parameters.Add("@EDateDeal", NewFullTextInfo.EDateDeal);
            dbCmd.Parameters.Add("@UpdateDate", NewFullTextInfo.UpdateDate);
            dbCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
                return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static void Update(NewFullTextInfo NewFullTextInfo)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("NewFullText_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@Id", NewFullTextInfo.Id);
            dbCmd.Parameters.Add("@PartnerId", NewFullTextInfo.PartnerId);
            dbCmd.Parameters.Add("@DealId", NewFullTextInfo.DealId);
            dbCmd.Parameters.Add("@Content", NewFullTextInfo.Content);
            dbCmd.Parameters.Add("@SDateDeal", NewFullTextInfo.SDateDeal);
            dbCmd.Parameters.Add("@EDateDeal", NewFullTextInfo.EDateDeal);
            dbCmd.Parameters.Add("@UpdateDate", NewFullTextInfo.UpdateDate);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static NewFullTextInfo GetInfo(int id)
        {
            NewFullTextInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("NewFullText_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@Id", id);
            try
            {
                dbConn.Open();
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.Read()) retVal = new NewFullTextInfo(dr);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable SearchContent_GetAndPaging(string phrase, int PageSize, int PageIndex, out int TotalRecord)
        {
            DataTable retVal = new DataTable();
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("SearchContent_GetAndPaging", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Phrase", phrase);
            dbCmd.Parameters.AddWithValue("@PageSize", PageSize);
            dbCmd.Parameters.AddWithValue("@PageIndex", PageIndex);
            dbCmd.Parameters.AddWithValue("@Count", SqlDbType.Int).Direction = ParameterDirection.Output;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                TotalRecord = (int)dbCmd.Parameters["@Count"].Value;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static void NeedInsertJob()
        {
            int partnerid, dealId, saleOff, cityid, parentcity, cateId, provinceId, stime, etime;
            string content, pName, proName, timeFrame, logo, contact, productname, provinceName;
            DateTime sdate, edate;

            DataTable dtNewToFullText = GetToInsertFullText();
            int count = dtNewToFullText.Rows.Count;
            if (count > 0)
            {
                for (int i = 0; i < count; i++)
                {
                    try
                    {
                        sdate = Convert.ToDateTime(dtNewToFullText.Rows[i]["SDateDeal"].ToString());
                    }
                    catch { sdate = DateTime.Now; }
                    try
                    {
                        edate = Convert.ToDateTime(dtNewToFullText.Rows[i]["EDateDeal"].ToString());
                    }
                    catch { edate = DateTime.Now; }

                    try
                    {
                        provinceName = Convert.ToString(dtNewToFullText.Rows[i]["provinceName"].ToString());
                    }
                    catch { provinceName = ""; }
                    try
                    {
                        provinceId = Convert.ToInt32(dtNewToFullText.Rows[i]["provinceId"].ToString());
                    }
                    catch { provinceId = -1; }

                    try
                    {
                        pName = Convert.ToString(dtNewToFullText.Rows[i]["pName"].ToString());
                    }
                    catch { pName = ""; }

                    try
                    {
                        contact = Convert.ToString(dtNewToFullText.Rows[i]["Contact"].ToString());
                    }
                    catch { contact = ""; }


                    try
                    {
                        proName = Convert.ToString(dtNewToFullText.Rows[i]["proName"].ToString());
                    }
                    catch { proName = ""; }

                    try
                    {
                        productname = Convert.ToString(dtNewToFullText.Rows[i]["ProductName"].ToString());
                    }
                    catch { productname = ""; }

                    try
                    {
                        logo = Convert.ToString(dtNewToFullText.Rows[i]["Logo"].ToString());
                    }
                    catch { logo = ""; }

                    try
                    {
                        partnerid = Convert.ToInt32(dtNewToFullText.Rows[i]["PartnerId"].ToString());
                    }
                    catch { partnerid = -1; }

                    try
                    {
                        cateId = Convert.ToInt32(dtNewToFullText.Rows[i]["CateId"].ToString());
                    }
                    catch { cateId = -1; }


                    try
                    {
                        cityid = Convert.ToInt32(dtNewToFullText.Rows[i]["CityId"].ToString());
                        parentcity = tbl_CityManager.GetParentId(cityid);
                    }
                    catch
                    {
                        cityid = -1;
                        parentcity = -1;
                    }

                    try
                    {
                        dealId = Convert.ToInt32(dtNewToFullText.Rows[i]["DealId"].ToString());
                    }
                    catch { dealId = -1; }

                    try
                    {
                        timeFrame = Convert.ToString(dtNewToFullText.Rows[i]["TimeFrame"].ToString());
                    }
                    catch { timeFrame = ""; }

                    try
                    {
                        saleOff = Convert.ToInt32(dtNewToFullText.Rows[i]["saleOff"].ToString());
                    }
                    catch { saleOff = -1; }

                    try
                    {
                        stime = Convert.ToInt32(dtNewToFullText.Rows[i]["stime"].ToString());
                    }
                    catch { stime = -1; }
                    try
                    {
                        etime = Convert.ToInt32(dtNewToFullText.Rows[i]["etime"].ToString());
                    }
                    catch { etime = -1; }

                    content = ".PNAME. " + pName + " " + StringUtility.UnicodeToKoDau(pName) + " ./PNAME..PRONAME. " +
                                    proName + " " + StringUtility.UnicodeToKoDau(proName) + " ./PRONAME..CONTACT. " + contact + " " + StringUtility.UnicodeToKoDau(contact) + " ./CONTACT.. SALEOFF. " +
                                    StringUtility.UnicodeToKoDau(saleOff.ToString()) + "%" + " ./SALEOFF.";
                    ;

                    int id = InsertFullText(partnerid, dealId, content, sdate, edate, timeFrame, saleOff, logo, cityid, contact, productname, parentcity, cateId, pName, provinceId, provinceName, stime, etime);
                    if (id > 0)
                    {

                        UpdateStatusInsertFullTextToPartNer(partnerid, 0);
                        UpdateStatusInsertFullTextToDeal(dealId, 0);
                    }
                }

            }
        }
        public static DataTable GetToUpdateFullText()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Partner_GetToUpdateFullText", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataTable GetToInsertFullText()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Partner_GetToInsertFullText", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static void UpdateStatusUpdateFullText(int id, int updateFI)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Partner_SetToUpdateFullText", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Id", id);
            dbCmd.Parameters.AddWithValue("@NeedUpdateFI", updateFI);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static int InsertFullText(int PartnerId, int DealId, string Content, DateTime SDateDeal, DateTime EDateDeal, string timeFrame, int saleOff, string logo, int cityid, string contact, string productname, int parentcity, int cateId, string partnerName, int provinceId, string provinceName, int stime, int etime)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("NewFullText_InsertFullText", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@PartnerId", PartnerId);
            dbCmd.Parameters.AddWithValue("@DealId", DealId);
            dbCmd.Parameters.AddWithValue("@Content", Content);
            dbCmd.Parameters.AddWithValue("@SDateDeal", SDateDeal);
            dbCmd.Parameters.AddWithValue("@EDateDeal", EDateDeal);
            dbCmd.Parameters.AddWithValue("@TimeFrame", timeFrame);
            dbCmd.Parameters.AddWithValue("@saleOff", saleOff);
            dbCmd.Parameters.AddWithValue("@Logo", logo);
            dbCmd.Parameters.AddWithValue("@CityId", cityid);
            dbCmd.Parameters.AddWithValue("@ParentCity", parentcity);
            dbCmd.Parameters.AddWithValue("@Contact", contact);
            dbCmd.Parameters.AddWithValue("@ProductName", productname);
            dbCmd.Parameters.AddWithValue("@CateId", cateId);

            dbCmd.Parameters.AddWithValue("@PName", partnerName);
            dbCmd.Parameters.AddWithValue("@provinceId", provinceId);
            dbCmd.Parameters.AddWithValue("@provinceName", provinceName);
            dbCmd.Parameters.AddWithValue("@stime", stime);
            dbCmd.Parameters.AddWithValue("@etime", etime);
            dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
                return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static void UpdateFullText(int PartnerId, int DealId, string Content, DateTime SDateDeal, DateTime EDateDeal)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("NewFullText_UpdateFullText", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@PartnerId", PartnerId);
            dbCmd.Parameters.AddWithValue("@DealId", DealId);
            dbCmd.Parameters.AddWithValue("@Content", Content);
            dbCmd.Parameters.AddWithValue("@SDateDeal", SDateDeal);
            dbCmd.Parameters.AddWithValue("@EDateDeal", EDateDeal);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static void UpdateStatusInsertFullTextToPartNer(int id, int insertFI)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Partner_SetToInsertFullText", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Id", id);
            dbCmd.Parameters.AddWithValue("@NeedInsertFI", insertFI);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static void UpdateStatusInsertFullTextToDeal(int id, int insertFI)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Product_SetToInsertFullText", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Id", id);
            dbCmd.Parameters.AddWithValue("@NeedInsertFI", insertFI);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static void DeleteFullText()
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("NewFullText_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static DataTable GetAllToFullText()
        {
            DataTable retVal = new DataTable();
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tbl_Partner_GetAllToFullText", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataTable GetAllToFullTextByNeedInsertFI()
        {
            DataTable retVal = new DataTable();
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tbl_Partner_GetAllToFullTextByNeedInsertFI", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable FullTextSearchAndPaging(string phrase, int cateId, DateTime datedeal, string filter, int parentCity,int stime,int etime, int PageSize, int PageIndex, out int TotalRecord)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("Search_GetAndPaging", dbConn);
            dbCmd.Parameters.AddWithValue("@Phrase", phrase);
            dbCmd.Parameters.AddWithValue("@CateId", cateId);
            dbCmd.Parameters.AddWithValue("@DateDeal", datedeal);
            dbCmd.Parameters.AddWithValue("@Filter", filter);
            dbCmd.Parameters.AddWithValue("@ParentCity", parentCity);

            dbCmd.Parameters.AddWithValue("@stime", stime);
            dbCmd.Parameters.AddWithValue("@etime", etime);

            dbCmd.Parameters.AddWithValue("@PageSize", PageSize);
            dbCmd.Parameters.AddWithValue("@PageIndex", PageIndex);
            dbCmd.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int));
            dbCmd.Parameters["@Count"].Direction = ParameterDirection.Output;
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                TotalRecord = Convert.ToInt32(dbCmd.Parameters["@Count"].Value);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable FullTextSearchAndPagingAll(string phrase, int cateId, DateTime datedeal, int stime, int etime, string filter, int parentCity, int PageSize, int PageIndex, out int TotalRecord)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("Search_GetAndPagingAll", dbConn);
            dbCmd.Parameters.AddWithValue("@Phrase", phrase);

            dbCmd.Parameters.AddWithValue("@DateDeal", datedeal);

            dbCmd.Parameters.AddWithValue("@stime", stime);
            dbCmd.Parameters.AddWithValue("@etime", etime);

            dbCmd.Parameters.AddWithValue("@ParentCity", parentCity);
            dbCmd.Parameters.AddWithValue("@PageSize", PageSize);
            dbCmd.Parameters.AddWithValue("@PageIndex", PageIndex);
            dbCmd.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int));
            dbCmd.Parameters["@Count"].Direction = ParameterDirection.Output;
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                TotalRecord = Convert.ToInt32(dbCmd.Parameters["@Count"].Value);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable tbl_Partner_GetAToFullTextByPartnerId(int partnerId)
        {
            DataTable retVal = new DataTable();
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tbl_Partner_GetAToFullTextByPartnerId", dbConn);
            dbCmd.Parameters.AddWithValue("@PartnerId", partnerId);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable tbl_Partner_GetAToFullTextByDealId(int dealId)
        {
            DataTable retVal = new DataTable();
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tbl_Partner_GetAToFullTextByPartnerId", dbConn);
            dbCmd.Parameters.AddWithValue("@PartnerId", dealId);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static void FullTextUpdateByDealId(int PartnerId, int DealId, string Content, DateTime SDateDeal, DateTime EDateDeal, string timeFrame, int saleOff, string logo, int cityid, string contact, string productname, int parentcity, int cateId, string partnerName, int provinceId, string provinceName)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Comments_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }


    }
}

